﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;

namespace Rookey.Frame.IMCore
{
	class SQLiteAccountStorage : IAccountStorage
	{
		string m_ConnectionString = "";

		public SQLiteAccountStorage()
		{
			string path = String.Format(@"{0}\App_Data\IM.db", ServerImpl.Instance.BaseDirecotry);
			m_ConnectionString = string.Format("Data Source=\"{0}\";Pooling=False", path);
		}

		DataRow IAccountStorage.GetAccountInfo(string name)
		{
			SQLiteConnection conn = new SQLiteConnection(m_ConnectionString);
			SQLiteCommand cmd = new SQLiteCommand();
			cmd.Connection = conn;
			cmd.CommandText = string.Format(
				"select * from Users u where u.UpperName=?",
				name
			);

			cmd.Parameters.Add("User", DbType.String).Value = name.ToUpper();

			SQLiteDataAdapter ada = new SQLiteDataAdapter();
			ada.SelectCommand = cmd;

			DataTable dt = new DataTable();
			ada.Fill(dt);
			ada.Dispose();

			return dt.Rows.Count > 0 ? dt.Rows[0] : null;
		}

		DataRow IAccountStorage.GetAccountInfo(Int64 key)
		{
			SQLiteConnection conn = new SQLiteConnection(m_ConnectionString);
			SQLiteCommand cmd = new SQLiteCommand();
			cmd.Connection = conn;
			cmd.CommandText = "select * from Users u where u.Key=?";

			cmd.Parameters.Add("User", DbType.Int64).Value = key;

			SQLiteDataAdapter ada = new SQLiteDataAdapter();
			ada.SelectCommand = cmd;

			DataTable dt = new DataTable();
			ada.Fill(dt);
			ada.Dispose();

			return dt.Rows.Count > 0 ? dt.Rows[0] : null;
		}

		DataRowCollection IAccountStorage.GetAllUsers()
		{
			SQLiteConnection conn = new SQLiteConnection(m_ConnectionString);
			SQLiteCommand cmd = new SQLiteCommand();
			cmd.Connection = conn;
			cmd.CommandText = @"
				select Key, Name, Nickname, EMail, RegisterTime 
				from Users u 
				where u.Type = 0
				order by RegisterTime desc";

			SQLiteDataAdapter ada = new SQLiteDataAdapter();
			ada.SelectCommand = cmd;

			DataTable dt = new DataTable();
			ada.Fill(dt);
			ada.Dispose();

			return dt.Rows;
		}

		DataRowCollection IAccountStorage.GetAllGroups()
		{
			SQLiteConnection conn = new SQLiteConnection(m_ConnectionString);
			SQLiteCommand cmd = new SQLiteCommand();
			cmd.Connection = conn;
			cmd.CommandText = @"
				select u.Key, u.Name, u.Nickname, u.EMail, u.RegisterTime, c.Key Creator
				from Users u, Users c, UserRelationShip ur 
				where u.Type = 1 and u.Key = ur.HostKey and c.Key = ur.GuestKey and ur.Relationship = 3
				order by u.RegisterTime desc";

			SQLiteDataAdapter ada = new SQLiteDataAdapter();
			ada.SelectCommand = cmd;

			DataTable dt = new DataTable();
			ada.Fill(dt);
			ada.Dispose();

			return dt.Rows;
		}

		DataRowCollection IAccountStorage.GetFriends(string name)
		{
			DataTable result = new DataTable();

			SQLiteConnection conn = new SQLiteConnection(m_ConnectionString);
			SQLiteCommand cmd = new SQLiteCommand();
			cmd.Connection = conn;
			cmd.CommandText =
			@"select 
				guest.Name as Name,
				guest.Type as Type,
				r.RenewTime as RenewTime,
				r.Relationship as Relationship
			from 
				UserRelationship r,
				Users host,
				Users guest
			where 
				r.HostKey=host.Key and
				r.GuestKey=guest.Key and
				host.UpperName=?
			";

			cmd.Parameters.Add("user", DbType.String).Value = name.ToUpper();

			SQLiteDataAdapter ada = new SQLiteDataAdapter();
			ada.SelectCommand = cmd;

			ada.Fill(result);
			ada.Dispose();

			return result.Rows;
		}

		Int64 IAccountStorage.GetRelationship(string account1, string account2)
		{
			SQLiteConnection conn = new SQLiteConnection(m_ConnectionString);
			SQLiteCommand cmd = new SQLiteCommand();
			cmd.Connection = conn;
			cmd.CommandText = @"
				select *
				from Users host,Users guest,UserRelationship r
				where host.UpperName=? and guest.UpperName=? and r.HostKey=host.Key and r.GuestKey=guest.Key
			";

			cmd.Parameters.Add("User1", DbType.String).Value = account1.ToUpper();
			cmd.Parameters.Add("User2", DbType.String).Value = account2.ToUpper();

			DataTable result = new DataTable();

			SQLiteDataAdapter ada = new SQLiteDataAdapter();

			ada.SelectCommand = cmd;
			ada.Fill(result);
			ada.Dispose();

			return result.Rows.Count > 0 ? Convert.ToInt64(result.Rows[0]["Relationship"]) : -1;
		}

		bool IAccountStorage.Validate(string userId, string password)
		{
			SQLiteConnection conn = new SQLiteConnection(m_ConnectionString);
			SQLiteCommand cmd = new SQLiteCommand();
			cmd.Connection = conn;
			cmd.CommandText = string.Format(
				"select * from Users where UpperName=? and Password=?",
				userId, password
			);

			cmd.Parameters.Add("User", DbType.String).Value = userId.ToUpper();
			cmd.Parameters.Add("Password", DbType.String).Value = Utility.MD5(password);

			SQLiteDataAdapter ada = new SQLiteDataAdapter();
			ada.SelectCommand = cmd;

			DataTable dt = new DataTable();
			ada.Fill(dt);
			ada.Dispose();

			return dt.Rows.Count > 0;
		
		}

		String[] IAccountStorage.GetUserRoles(string userId)
		{
			SQLiteConnection conn = new SQLiteConnection(m_ConnectionString);
			SQLiteCommand cmd = new SQLiteCommand();
			cmd.Connection = conn;
			cmd.CommandText = string.Format(
				"select r.Name as RoleName from Users u,User_Role ur,Roles r where u.UpperName=? and u.Key=ur.UserKey and ur.RoleKey=r.Key",
				userId
			);

			cmd.Parameters.Add("User", DbType.String).Value = userId.ToUpper();

			SQLiteDataAdapter ada = new SQLiteDataAdapter();
			ada.SelectCommand = cmd;

			DataTable dt = new DataTable();
			ada.Fill(dt);
			ada.Dispose();

			List<string> names = new List<string>();
			foreach (DataRow row in dt.Rows) names.Add(row["RoleName"] as string);

			return names.ToArray();
		}

		void IAccountStorage.AddFriend(string user, string friend)
		{
			SQLiteConnection conn = new SQLiteConnection(m_ConnectionString);
			SQLiteCommand cmd = new SQLiteCommand();
			cmd.Connection = conn;
			cmd.CommandText = @"
						insert into UserRelationship (HostKey,GuestKey,Relationship,RenewTime)
						select host.Key as HostKey,guest.Key as GuestKey,0,? as Relationship
						from Users host,Users guest
						where (host.UpperName=? or host.UpperName=?) and (guest.UpperName=? or guest.UpperName=?) and host.Key<>guest.Key
					";

			cmd.Parameters.Add("RenewTime", DbType.DateTime).Value = DateTime.Now;
			cmd.Parameters.Add("Host1", DbType.String).Value = user.ToUpper();
			cmd.Parameters.Add("Client1", DbType.String).Value = friend.ToUpper();
			cmd.Parameters.Add("Host2", DbType.String).Value = user.ToUpper();
			cmd.Parameters.Add("Client2", DbType.String).Value = friend.ToUpper();

			conn.Open();
			try
			{
				cmd.ExecuteNonQuery();
			}
			finally
			{
				conn.Close();
			}
		}

		void IAccountStorage.DeleteFriend(Int64 userId, Int64 friendId)
		{
			SQLiteConnection conn = new SQLiteConnection(m_ConnectionString);
			SQLiteCommand cmd = new SQLiteCommand();
			cmd.Connection = conn;
			cmd.CommandText = @"
				delete from UserRelationship
				where (HostKey=? and GuestKey=?) or (HostKey=? and GuestKey=?)
			";

			cmd.Parameters.Add("Host1", DbType.Int64).Value = userId;
			cmd.Parameters.Add("Client1", DbType.Int64).Value = friendId;
			cmd.Parameters.Add("Host2", DbType.Int64).Value = friendId;
			cmd.Parameters.Add("Client2", DbType.Int64).Value = userId;

			conn.Open();
			try
			{
				cmd.ExecuteNonQuery();
			}
			finally
			{
				conn.Close();
			}
		}

		void IAccountStorage.DeleteUser(Int64 id)
		{

			SQLiteConnection conn = new SQLiteConnection(m_ConnectionString);
			conn.Open();
			SQLiteTransaction tran = conn.BeginTransaction();
			try
			{
				SQLiteCommand cmd = new SQLiteCommand(
					@"delete from UserRelationship where HostKey = ? or GuestKey = ?;
					delete from Users where Key = ?;
					delete from User_Role where UserKey = ?"
				);
				cmd.Parameters.Add("P1", DbType.Int64).Value = id;
				cmd.Parameters.Add("P2", DbType.Int64).Value = id;
				cmd.Parameters.Add("P3", DbType.Int64).Value = id;
				cmd.Parameters.Add("P4", DbType.Int64).Value = id;
				cmd.Connection = conn;
				cmd.ExecuteNonQuery();

				tran.Commit();
			}
			catch
			{
				tran.Rollback();
				throw;
			}
			finally
			{
				conn.Close();
			}
		}
		void IAccountStorage.DeleteGroup(Int64 id)
		{
			SQLiteConnection conn = new SQLiteConnection(m_ConnectionString);
			conn.Open();
			SQLiteTransaction tran = conn.BeginTransaction();
			try
			{
				SQLiteCommand deleteUR = new SQLiteCommand(
					@"delete from User_Role where UserKey=?", conn
				);
				deleteUR.Parameters.Add("Key", DbType.Int64).Value = id;
				deleteUR.ExecuteNonQuery();

				SQLiteCommand deleteRelationship = new SQLiteCommand(
					@"delete from UserRelationship where HostKey=? or GuestKey=?", conn
				);
				deleteRelationship.Parameters.Add("Key1", DbType.Int64).Value = id;
				deleteRelationship.Parameters.Add("Key2", DbType.Int64).Value = id;
				deleteRelationship.ExecuteNonQuery();

				SQLiteCommand deleteUser = new SQLiteCommand(
					@"delete from Users where Key=?", conn
				);
				deleteUser.Parameters.Add("Key", DbType.Int64).Value = id;
				deleteUser.ExecuteNonQuery();

				tran.Commit();
			}
			catch
			{
				tran.Rollback();
				throw;
			}
			finally
			{
				conn.Close();
			}
		}

		void IAccountStorage.CreateUser(String name, String nickname, String password, String email)
		{
			SQLiteConnection conn = new SQLiteConnection(m_ConnectionString);
			conn.Open();
			try
			{
				SQLiteTransaction tran = conn.BeginTransaction();
				try
				{
					SQLiteCommand selectCmd = new SQLiteCommand(
						@"select [Key] from Users where UpperName=?", conn
					);
					selectCmd.Parameters.Add("Name", DbType.String).Value = name.ToUpper();
					object key = selectCmd.ExecuteScalar();
					if (key != null && key != DBNull.Value)
					{
						throw new Exception(String.Format("用户\"{0}\"已存在！", name));
					}

					SQLiteCommand insertUser = new SQLiteCommand(
						@"
							insert into Users (Name,UpperName,Password,Nickname,Type,EMail,InviteCode,IsTemp,RegisterTime) values (?,?,?,?,?,?,?,0,?)
							", conn
					);
					insertUser.Parameters.Add("Name", DbType.String).Value = name;
					insertUser.Parameters.Add("UpperName", DbType.String).Value = name.ToUpper();
					insertUser.Parameters.Add("Password", DbType.String).Value = Utility.MD5(password);
					insertUser.Parameters.Add("Nickname", DbType.String).Value = nickname;
					insertUser.Parameters.Add("Type", DbType.Int64).Value = 0;
					insertUser.Parameters.Add("EMail", DbType.String).Value = email;
					insertUser.Parameters.Add("InviteCode", DbType.String).Value = Guid.NewGuid().ToString().ToUpper().Replace("-", "");
					insertUser.Parameters.Add("RegisterTime", DbType.DateTime).Value = DateTime.Now;
					insertUser.ExecuteNonQuery();

					SQLiteCommand insertUR = new SQLiteCommand(
						@"
							insert into User_Role (UserKey,RoleKey)
							select [Key] as UserKey,2 as RoleKey from Users where UpperName=?;

							insert into UserRelationShip(HostKey, GuestKey,RelationShip,RenewTime)
							select u1.Key, u2.Key, 0, datetime('now','localtime') as RenewTime
							from Users u1, Users u2 
							where u1.Name='public' and u2.UpperName=?;

							insert into UserRelationShip(HostKey, GuestKey,RelationShip,RenewTime)
							select u2.Key, u1.Key, 0, datetime('now','localtime') as RenewTime
							from Users u1, Users u2 
							where u1.Name='public' and u2.UpperName=?;

							insert into UserRelationShip(HostKey, GuestKey,RelationShip,RenewTime)
							select u1.Key, u2.Key, 0, datetime('now','localtime') as RenewTime
							from Users u1, Users u2 
							where u1.Name='admin' and u2.UpperName=?;

							insert into UserRelationShip(HostKey, GuestKey,RelationShip,RenewTime)
							select u2.Key, u1.Key, 0, datetime('now','localtime') as RenewTime
							from Users u1, Users u2 
							where u1.Name='admin' and u2.UpperName=?;",
						conn
					);
					string upperName = name.ToUpper();
					insertUR.Parameters.Add("Name", DbType.String).Value = upperName;
					insertUR.Parameters.Add("Name1", DbType.String).Value = upperName;
					insertUR.Parameters.Add("Name2", DbType.String).Value = upperName;
					insertUR.Parameters.Add("Name3", DbType.String).Value = upperName;
					insertUR.Parameters.Add("Name4", DbType.String).Value = upperName;

					insertUR.ExecuteNonQuery();

					tran.Commit();
				}
				catch
				{
					tran.Rollback();
					throw;
				}
			}
			finally
			{
				conn.Close();
			}
        }

        void IAccountStorage.CreateTempUser(string name, string nickname)
        {
            SQLiteConnection conn = new SQLiteConnection(m_ConnectionString);
            conn.Open();
            try
            {
                SQLiteTransaction tran = conn.BeginTransaction();
                try
                {
                    SQLiteCommand insertUser = new SQLiteCommand(
                        @"
							insert into Users (Name,UpperName,Password,Nickname,Type,EMail,InviteCode,IsTemp,RegisterTime) 
                            values (?,?,?,?,?,?,?,1,?)
						", conn
                    );
                    insertUser.Parameters.Add("Name", DbType.String).Value = name;
                    insertUser.Parameters.Add("UpperName", DbType.String).Value = name.ToUpper();
                    insertUser.Parameters.Add("Password", DbType.String).Value = "";
                    insertUser.Parameters.Add("Nickname", DbType.String).Value = nickname;
                    insertUser.Parameters.Add("Type", DbType.Int64).Value = 0;
                    insertUser.Parameters.Add("EMail", DbType.String).Value = "";
                    insertUser.Parameters.Add("InviteCode", DbType.String).Value = Guid.NewGuid().ToString().ToUpper().Replace("-", "");
                    insertUser.Parameters.Add("RegisterTime", DbType.DateTime).Value = DateTime.Now;
                    insertUser.ExecuteNonQuery();

                    SQLiteCommand insertUR = new SQLiteCommand(
                        @"
							insert into User_Role (UserKey,RoleKey)
							select [Key] as UserKey,2 as RoleKey from Users where UpperName=?;
                           ",
                        conn
                    );
                    string upperName = name.ToUpper();
                    insertUR.Parameters.Add("Name", DbType.String).Value = upperName;
                    insertUR.Parameters.Add("Name1", DbType.String).Value = upperName;
                    insertUR.Parameters.Add("Name2", DbType.String).Value = upperName;
                    insertUR.Parameters.Add("Name3", DbType.String).Value = upperName;
                    insertUR.Parameters.Add("Name4", DbType.String).Value = upperName;

                    insertUR.ExecuteNonQuery();

                    tran.Commit();
                }
                catch
                {
                    tran.Rollback();
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }
        }

		void IAccountStorage.CreateGroup(String creator, String name, String nickname)
		{
			SQLiteConnection conn = new SQLiteConnection(m_ConnectionString);
			conn.Open();
			SQLiteTransaction tran = conn.BeginTransaction();
			try
			{
				SQLiteCommand selectCmd = new SQLiteCommand(
					@"select [Key] from Users where UpperName=?", conn
				);
				selectCmd.Parameters.Add("Name", DbType.String).Value = name.ToUpper();
				object key = selectCmd.ExecuteScalar();
				if (key != null && key != DBNull.Value)
				{
					throw new Exception(String.Format("群\"{0}\"已存在！", name));
				}

				SQLiteCommand insertUser = new SQLiteCommand(
					@"
					insert into Users (Name,UpperName,Password,Nickname,Type,EMail,InviteCode,IsTemp,RegisterTime) values (?,?,?,?,?,?,?,0,?)
					", conn
				);
				insertUser.Parameters.Add("Name", DbType.String).Value = name;
				insertUser.Parameters.Add("UpperName", DbType.String).Value = name.ToUpper();
				insertUser.Parameters.Add("Password", DbType.String).Value = "";
				insertUser.Parameters.Add("Nickname", DbType.String).Value = nickname;
				insertUser.Parameters.Add("Type", DbType.Int64).Value = 1;
				insertUser.Parameters.Add("EMail", DbType.String).Value = "";
				insertUser.Parameters.Add("InviteCode", DbType.String).Value = Guid.NewGuid().ToString().ToUpper().Replace("-", "");
				insertUser.Parameters.Add("RegisterTime", DbType.DateTime).Value = DateTime.Now;
				insertUser.ExecuteNonQuery();

				SQLiteCommand insertUR = new SQLiteCommand(
					@"
					insert into User_Role (UserKey,RoleKey)
					select [Key] as UserKey,2 as RoleKey from Users where UpperName=?
					", conn
				);
				insertUR.Parameters.Add("Name", DbType.String).Value = name.ToUpper();
				insertUR.ExecuteNonQuery();

				SQLiteCommand insertRelationship = new SQLiteCommand(
					@"
					insert into UserRelationship (RenewTime,HostKey,GuestKey,Relationship)
					select ? as RenewTime,(select Key from Users where UpperName=?) as HostKey,(select Key from Users where UpperName=?) as GuestKey,3 as Relationship
					",
					conn
				);
				insertRelationship.Parameters.Add("RenewTime", DbType.DateTime).Value = DateTime.Now;
				insertRelationship.Parameters.Add("Host", DbType.String);
				insertRelationship.Parameters.Add("Guest", DbType.String);

				insertRelationship.Parameters["Host"].Value = name.ToUpper();
				insertRelationship.Parameters["Guest"].Value = creator.ToUpper();
				insertRelationship.ExecuteNonQuery();

				insertRelationship.Parameters["Host"].Value = creator.ToUpper();
				insertRelationship.Parameters["Guest"].Value = name.ToUpper();
				insertRelationship.ExecuteNonQuery();

				tran.Commit();
			}
			catch
			{
				tran.Rollback();
				throw;
			}
			finally
			{
				conn.Close();
			}
		}

		void IAccountStorage.UpdateUserInfo(string name, Hashtable values)
		{
			SQLiteConnection conn = new SQLiteConnection(m_ConnectionString);

			conn.Open();
			try
			{
				if (values.ContainsKey("Password"))
				{
					if (!values.ContainsKey("PreviousPassword")) throw new Exception("原密码错误！");

					SQLiteCommand checkPwdCmd = new SQLiteCommand();
					checkPwdCmd.Connection = conn;
					checkPwdCmd.CommandText = "select [Key] from Users where UpperName = ? and Password = ?";

					checkPwdCmd.Parameters.Add("Name", DbType.String).Value = name.ToUpper();
					checkPwdCmd.Parameters.Add("Password", DbType.String).Value = Utility.MD5(values["PreviousPassword"].ToString());

					object val = checkPwdCmd.ExecuteScalar();

					if (val == null) throw new Exception("原密码错误！");
				}

				StringBuilder cmdText = new StringBuilder();
				cmdText.Append("update Users set Name = Name");
				if (values.ContainsKey("Nickname")) cmdText.Append(",Nickname = ?");
				if (values.ContainsKey("Password")) cmdText.Append(",Password = ?");
				if (values.ContainsKey("EMail")) cmdText.Append(",EMail = ?");
				if (values.ContainsKey("InviteCode")) cmdText.Append(",InviteCode = ?");
				if (values.ContainsKey("AcceptStrangerIM")) cmdText.Append(",AcceptStrangerIM = ?");
				if (values.ContainsKey("MsgFileLimit")) cmdText.Append(",MsgFileLimit = ?");
				if (values.ContainsKey("MsgImageLimit")) cmdText.Append(",MsgImageLimit = ?");
				if (values.ContainsKey("HomePage")) cmdText.Append(",HomePage = ?");
				if (values.ContainsKey("HeadIMG")) cmdText.Append(",HeadIMG = ?");
				if (values.ContainsKey("Remark")) cmdText.Append(",Remark = ?");
				cmdText.Append(" where UpperName=?");
				if (values.ContainsKey("PreviousPassword")) cmdText.Append(" and Password = ?");
				SQLiteCommand cmd = new SQLiteCommand();
				cmd.Connection = conn;
				cmd.CommandText = cmdText.ToString();

				if (values.ContainsKey("Nickname")) cmd.Parameters.Add("Nickname", DbType.String).Value = values["Nickname"];
				if (values.ContainsKey("Password")) cmd.Parameters.Add("Password", DbType.String).Value = Utility.MD5(values["Password"] as string);
				if (values.ContainsKey("EMail")) cmd.Parameters.Add("EMail", DbType.String).Value = values["EMail"];
				if (values.ContainsKey("InviteCode")) cmd.Parameters.Add("InviteCode", DbType.String).Value = values["InviteCode"];

				if (values.ContainsKey("AcceptStrangerIM")) cmd.Parameters.Add("AcceptStrangerIM", DbType.Int64).Value = ((bool)values["AcceptStrangerIM"]) ? 1 : 0;
				if (values.ContainsKey("MsgFileLimit")) cmd.Parameters.Add("MsgFileLimit", DbType.Int64).Value = Convert.ToInt64((Double)values["MsgFileLimit"]);
				if (values.ContainsKey("MsgImageLimit")) cmd.Parameters.Add("MsgImageLimit", DbType.Int64).Value = Convert.ToInt64((Double)values["MsgImageLimit"]);

				if (values.ContainsKey("HomePage")) cmd.Parameters.Add("HomePage", DbType.String).Value = values["HomePage"];
				if (values.ContainsKey("HeadIMG")) cmd.Parameters.Add("HeadIMG", DbType.String).Value = values["HeadIMG"];
				if (values.ContainsKey("Remark")) cmd.Parameters.Add("Remark", DbType.String).Value = values["Remark"];

				cmd.Parameters.Add("Name", DbType.String).Value = name.ToUpper();
				if (values.ContainsKey("PreviousPassword")) cmd.Parameters.Add("PreviousPassword", DbType.String).Value = Utility.MD5(values["PreviousPassword"] as string);

				cmd.ExecuteNonQuery();
			}
			finally
			{
				conn.Close();
			}
		}

		String[] IAccountStorage.GetGroupManagers(string name)
		{
			DataTable result = new DataTable();

			SQLiteConnection conn = new SQLiteConnection(m_ConnectionString);
			SQLiteCommand cmd = new SQLiteCommand();
			cmd.Connection = conn;
			cmd.CommandText =
				@"select 
					guest.Name as Name
				from 
					UserRelationship r,
					Users host,
					Users guest
				where 
					r.Relationship=2 and
					r.HostKey=host.Key and
					r.GuestKey=guest.Key and
					host.UpperName=?
				";

			cmd.Parameters.Add("user", DbType.String).Value = name.ToUpper();

			SQLiteDataAdapter ada = new SQLiteDataAdapter();
			ada.SelectCommand = cmd;

			ada.Fill(result);
			ada.Dispose();

			List<String> names = new List<string>();
			foreach (DataRow row in result.Rows) names.Add(row["Name"] as string);

			return names.ToArray();
		}

	}
}
